# Datum richtig formatieren
weather$date <- dmy(weather$date)

# Temperatur in Celsius umrechnen
weather <- weather %>%
  mutate(maximum.temperature = round((maximum.temperature-32)*5/9, 2),
         minimum.temperature = round((minimum.temperature-32)*5/9, 2),
         average.temperature = round((average.temperature-32)*5/9, 2))

levels(weather$precipitation)
levels(weather$snow.fall)
levels(weather$snow.depth)

# Niederschlagsmenge formatieren, T-Werte durch 100 ersetzen und zurückformatieren
weather$precipitation <- as.character(weather$precipitation)
weather$precipitation[weather$precipitation == "T"] <- 100
weather$precipitation <- as.double(weather$precipitation)

# Schneefall und -tiefe formatieren, T-Werte durch 100 ersetzten und zurückformatieren
weather$snow.fall <- as.character(weather$snow.fall)
weather$snow.fall[weather$snow.fall == "T"] <- 100
weather$snow.fall <- as.double(weather$snow.fall)

weather$snow.depth <- as.character(weather$snow.depth)
weather$snow.depth[weather$snow.depth == "T"] <- 100
weather$snow.depth <- as.double(weather$snow.depth)

# Regen und Schnee in mm umrechen
weather <- weather %>%
  mutate(precipitation = round(precipitation*25.4, 2),
         snow.fall = round(snow.fall*25.4, 2),
         snow.depth = round(snow.depth*25.4, 2))

# Die ehemaligen T-Werte (jetzt 2540) mit 0.01 ersetzten
max(weather$precipitation)
weather$precipitation[weather$precipitation == 2540] <- 0.01

max(weather$snow.fall)
weather$snow.fall[weather$snow.fall == 2540] <- 0.01

max(weather$snow.depth)
weather$snow.depth[weather$snow.depth == 2540] <- 0.01

# Wetterdaten speichern
saveRDS(weather, "Data/weather_daily_2016.rds")

# Wetterdaten laden
weather <- readRDS("Data/weather_daily_2016.rds")

# Daten laden
weather17 <- read_csv("Data/daily_weather_2017.csv")

# Daten für 2017 auswählen, Spaltennamen dem Vorjahr anpassen und Durchschnittstemperatur berechnen
weather17 <- weather17 %>%
  filter(year(DATE) == 2017) %>%
  select(date = DATE,
         maximum.temperature = TMAX,
         minimum.temperature = TMIN,
         precipitation = PRCP,
         snow.fall = SNOW,
         snow.depth = SNWD) %>%
  mutate(average.temperature = (minimum.temperature+maximum.temperature)/2)

# Fehlende Werte überprüfen
apply(df, 2, function(x) sum(is.na(x)))

# Einheiten umrechnen
weather17 <- weather17 %>%
  mutate(maximum.temperature = round((maximum.temperature-32)*5/9, 2),
         minimum.temperature = round((minimum.temperature-32)*5/9, 2),
         average.temperature = round((average.temperature-32)*5/9, 2),
         precipitation = round(precipitation*25.4, 2),
         snow.fall = round(snow.fall*25.4, 2),
         snow.depth = round(snow.depth*25.4, 2))

# Data Frame wie Vorjahr sortieren
weather17 <- weather17[,c(1:3,7,4:6)]

# Datensatz speichern
saveRDS(weather17, "Data/weather_daily_2017.RDS")

# Wetterdaten laden
weather_2017 <- read_rds("Data/weather_daily_2017.RDS")

# Nur Wetterdaten aus 2016 behalten
hourly_weather <- hourly_weather %>%
  filter(date(pickup_datetime) > "2015-12-31" & date(pickup_datetime) < "2017-01-01")

# Fehlende Werte überprüfen
apply(hourly_weather, 2, function(x) sum(is.na(x)))

# Spalten auswählen
hourly_weather <- hourly_weather %>%
  select(pickup_datetime, tempm, wspdm, fog, rain, snow, hail, thunder, tornado)

# Auf fehlende Werte überprüfen und diese interpolieren
apply(hourly_weather, 2, function(x) sum(is.na(x)))
hourly_weather[is.na(hourly_weather$tempm),]
hourly_weather$tempm <- na_interpolation(hourly_weather$tempm, option = "spline")
hourly_weather[is.na(hourly_weather$wspdm),]
hourly_weather$wspdm <- na_interpolation(hourly_weather$wspdm, option = "spline")
apply(hourly_weather, 2, function(x) sum(is.na(x)))

# Spalten umbenennen
hourly_weather <- hourly_weather %>%
  rename(datetime = pickup_datetime,
         temp = tempm,
         wspd = wspdm)

# Datum und Stunde trennen
hourly_weather <- hourly_weather %>%
  mutate(date = as_date(datetime),
         hour = hour(datetime))
hourly_weather$datetime <- NULL         

# Reihenfolge der Spalten festlegen und nur einen Messwert je Stunde behalten
hourly_weather <- hourly_weather[,c(9,10,1:8)]
hourly_weather <- hourly_weather %>% distinct(date, hour, .keep_all = T)

# Daten speichern
saveRDS(hourly_weather, "hourly_weather.RDS")

# Daten einlesen
df <- read_csv("Data/hourly_weather_new.csv")

# stündliche Daten auswählen
df <- df %>% select(DATE, HourlyAltimeterSetting, HourlyDewPointTemperature, HourlyDryBulbTemperature, 
                    HourlyPrecipitation, HourlyPresentWeatherType, HourlyPressureChange,   HourlyPressureTendency,
                    HourlyRelativeHumidity, HourlySeaLevelPressure, HourlySkyConditions,  HourlyStationPressure,
                    HourlyVisibility, HourlyWetBulbTemperature, HourlyWindDirection, HourlyWindGustSpeed, 
                    HourlyWindSpeed)

# Fehlende Werte überprüfen
apply(df, 2, function(x) sum(is.na(x)))

# Temperatur, Windgeschwindigkeit und Niederschlagsmenge auswählen
df <- df %>% select(DATE, HourlyDryBulbTemperature, HourlyPrecipitation, HourlyWindSpeed)

# Datensatz nach Jahren 2016 und 2017 aufsplitten
df16 <- df %>%
  filter(year(DATE)==2016)
df17 <- df %>%
  filter(year(DATE)==2017)

# Jeweils fehlende Werte überprüfen
apply(df16, 2, function(x) sum(is.na(x)))
apply(df17, 2, function(x) sum(is.na(x)))

# 2016
# Datum und Stunde trennen, sowie Uhrzeit als eigene Spalte behalten
df16 <- df16 %>%
  mutate(date = as_date(DATE),
         hour = hour(DATE),
         time = paste(hour(DATE), minute(DATE), second(DATE), sep = ":"))

# Alle Zeilen mit Uhrzeit 23:59:00 entfernen, da letzter Tagesreport mit allen Werten NA
df16 <- df16 %>% 
  filter(time != "23:59:0")
df16$time <- NULL
df16$DATE <- NULL

# Temperatur und Windgeschwindigkeit umrechnen
df16 <- df16 %>%
  mutate(temp = round((HourlyDryBulbTemperature-32)*5/9, 2),
         wdsp = round(HourlyWindSpeed*1.609344, 2))
df16$HourlyDryBulbTemperature <- NULL
df16$HourlyWindSpeed <- NULL

#Niederschlagsmenge umbenennen, Werte mit Buchstaben korrigieren, T durch 1000 ersetzten, in mm umrechnen und die ehemaligen T-Werte in 0.01mm ändern
df16 <- df16 %>%
  rename(precip = HourlyPrecipitation)

df16$precip <- str_replace_all(df16$precip, c("s" = ""))
df16$precip[df16$precip == "T"] <- 1000
df16$precip <- as.double(df16$precip)

df16 <- df16 %>%
  mutate(precip = round(precip*25.4, 2))
max(df16$precip, na.rm = T)
df16$precip[df16$precip == 25400] <- 0.01

# Reihenfolge der Spalten ändern und nur eine Zeile pro Stunde behalten
df16 <- df16[,c(2,3,4,5,1)]
df16 <- df16 %>% 
  distinct(date, hour, .keep_all = T)

# Fehlende Werte interpolieren
df16[,3:5] <- na_interpolation(df16[,3:5], option = "spline")
apply(df16, 2, function(x) sum(is.na(x)))

# 2017
# Datum und Stunde trennen, sowie Uhrzeit als eigene Spalte behalten
df17 <- df17 %>%
  mutate(date = as_date(DATE),
         hour = hour(DATE),
         time = paste(hour(DATE), minute(DATE), second(DATE), sep = ":"))

# Alle Zeilen mit Uhrzeit 23:59:00 entfernen, da letzter Tagesreport mit allen Werten NA
df17 <- df17 %>% 
  filter(time != "23:59:0")
df17$time <- NULL
df17$DATE <- NULL

# Temperatur und Windgeschwindigkeit umrechnen
df17 <- df17 %>%
  mutate(temp = round((HourlyDryBulbTemperature-32)*5/9, 2),
         wdsp = round(HourlyWindSpeed*1.609344, 2))
df17$HourlyDryBulbTemperature <- NULL
df17$HourlyWindSpeed <- NULL

# Niederschlagsmenge umbenennen, Werte mit Buchstaben korrigieren, T durch 1000 ersetzten, in mm umrechnen und die ehemaligen T-Werte in 0.01mm ändern
df17 <- df17 %>%
  rename(precip = HourlyPrecipitation)

df17$precip <- str_replace_all(df17$precip, c("s" = ""))
df17$precip[df17$precip == "T"] <- 1000
df17$precip <- as.double(df17$precip)

df17 <- df17 %>%
  mutate(precip = round(precip*25.4, 2))
max(df17$precip, na.rm = T)
df17$precip[df17$precip == 25400] <- 0.01

# Reihenfolge der Spalten ändern und nur eine Zeile pro Stunde behalten
df17 <- df17[,c(2,3,4,5,1)]
df17 <- df17 %>% 
  distinct(date, hour, .keep_all = T)

# Fehlende Werte interpolieren
df17[,3:5] <- na_interpolation(df17[,3:5], option = "spline")
apply(df17, 2, function(x) sum(is.na(x)))

# Daten speichern
saveRDS(df16, "Data/hourly_weather_2016.RDS")
saveRDS(df17, "Data/hourly_weather_2017.RDS")

#Daten laden
hourly_weather_16 <- read_rds("Data/hourly_weather_2016.RDS")
hourly_weather_17 <- read_rds("Data/hourly_weather_2017.RDS")

# Datensatz mit getrenntem Datum und Stunde erstellen
bike_q1 <- bike
bike_q1 <- bike_q1 %>%
  mutate(start_date = as_date(starttime),
         start_hour = hour(starttime),
         stop_date = as_date(stoptime),
         stop_hour = hour(stoptime))

# Datensatz für stündliche Starts je Station erstellen und Spalten für die Anzahl der losfahrenden Radfahrer, deren durchschnittliches Alter und die durchschnittliche Fahrtdauer einfügen, sowie Variable für Wochenende
hourly_starts <- bike_q1 %>%
  group_by(start_date, start_hour, start_station_id, start_station_name, start_station_latitude,
           start_station_longitude) %>%
  summarise(user_count = n(),
            avg_age = round(2016 - mean(birth_year)),
            avg_tripduration = mean(tripduration)) %>%
  mutate(weekend = ifelse(wday(start_date) == 6 | wday(start_date) == 7, 1,0))

# Datensatz erstellen der nach Stunde, Station und Geschlecht getrennt Fahrer zählt
gender <- bike_q1 %>%
  group_by(start_date, start_hour, start_station_id, gender) %>%
  summarise(user = n())
gender <- pivot_wider(gender, names_from = gender, values_from = user)

# Beide Datensätze verbinden und redundante Spalten löschen
hourly_starts <- cbind(hourly_starts, gender)
hourly_starts$start_date1 <- NULL
hourly_starts$start_hour1 <- NULL
hourly_starts$start_station_id1 <- NULL

# Datensatz erstellen, der nach Stunde, Station und Nutzertyp getrennt Fahrer zählt
usertype <- bike_q1 %>%
  group_by(start_date, start_hour, start_station_id, usertype) %>%
  summarise(user = n())
usertype <- pivot_wider(usertype, names_from = usertype, values_from = user)

# Beide Datensätze verbinden und redundante Spalten löschen
hourly_starts <- cbind(hourly_starts, usertype)
hourly_starts$start_date1 <- NULL
hourly_starts$start_hour1 <- NULL
hourly_starts$start_station_id1 <- NULL

# Spalten umbenennen
hourly_starts <- hourly_starts %>%
  rename(male_user_count = "1",
         female_user_count = "2",
         undefined_user_count = "0",
         subscriber_count = Subscriber,
         customer_count = Customer)

# Fehlende Werte ersetzen
hourly_starts$male_user_count[is.na(hourly_starts$male_user_count)] <- 0
hourly_starts$female_user_count[is.na(hourly_starts$female_user_count)] <- 0
hourly_starts$undefined_user_count[is.na(hourly_starts$undefined_user_count)] <- 0
hourly_starts$subscriber_count[is.na(hourly_starts$subscriber_count)] <- 0
hourly_starts$customer_count[is.na(hourly_starts$customer_count)] <- 0

# Nicht mehr benötigte Datensätze löschen
rm(gender)
rm(usertype)

# Datensatz für stündliche Stops je Station erstellen und Spalten für die Anzahl der losfahrenden Radfahrer, deren durchschnittliches Alter und die durchschnittliche Fahrtdauer einfügen, sowie Variable für Wochenende
hourly_stops <- bike_q1 %>%
  group_by(stop_date, stop_hour, end_station_id, end_station_name, end_station_latitude,
           end_station_longitude) %>%
  summarise(user_count = n(),
            avg_age = round(2016 - mean(birth_year)),
            avg_tripduration = mean(tripduration)) %>%
  mutate(weekend = ifelse(wday(stop_date) == 6 | wday(stop_date) == 7, 1,0))

# Datensatz erstellen der nach Stunde, Station und Geschlecht getrennt Fahrer zählt
gender <- bike_q1 %>%
  group_by(stop_date, stop_hour, end_station_id, end_station_name, end_station_latitude,
           end_station_longitude,gender) %>%
  summarise(user = n())
gender <- pivot_wider(gender, names_from = gender, values_from = user)

# Beide Datensätze verbinden und redundante Spalten löschen
hourly_stops <- cbind(hourly_stops, gender)
hourly_stops$stop_date1 <- NULL
hourly_stops$stop_hour1 <- NULL
hourly_stops$end_station_id1 <- NULL
hourly_stops$end_station_name1 <- NULL 
hourly_stops$end_station_latitude1 <- NULL
hourly_stops$end_station_longitude1 <- NULL

# Datensatz erstellen, der nach Stunde, Station und Nutzertyp getrennt Fahrer zählt
usertype <- bike_q1 %>%
  group_by(stop_date, stop_hour, end_station_id, end_station_name, end_station_latitude,
           end_station_longitude, usertype) %>%
  summarise(user = n())
usertype <- pivot_wider(usertype, names_from = usertype, values_from = user)

# Beide Datensätze verbinden und redundante Spalten löschen
hourly_stops <- cbind(hourly_stops, usertype)
hourly_stops$stop_date1 <- NULL
hourly_stops$stop_hour1 <- NULL
hourly_stops$end_station_id1 <- NULL
hourly_stops$end_station_name1 <- NULL 
hourly_stops$end_station_latitude1 <- NULL
hourly_stops$end_station_longitude1 <- NULL

# Spalten umbenennen
hourly_stops <- hourly_stops %>%
  rename(male_user_count = "1",
         female_user_count = "2",
         undefined_user_count = "0",
         subscriber_count = Subscriber,
         customer_count = Customer)

# Fehlende Werte ersetzen
hourly_stops$male_user_count[is.na(hourly_stops$male_user_count)] <- 0
hourly_stops$female_user_count[is.na(hourly_stops$female_user_count)] <- 0
hourly_stops$undefined_user_count[is.na(hourly_stops$undefined_user_count)] <- 0
hourly_stops$subscriber_count[is.na(hourly_stops$subscriber_count)] <- 0
hourly_stops$customer_count[is.na(hourly_stops$customer_count)] <- 0

# Nicht mehr benötigte Datensätze löschen
rm(gender)
rm(usertype)

# Aggregierte Datensätze speichern
saveRDS(hourly_starts, "Data/bike_q1_starts.rds")
saveRDS(hourly_stops, "Data/bike_q1_stops.rds")

# Datensätze laden
hourly_starts <- readRDS("Data/bike_q1_starts.rds")
hourly_stops <- readRDS("Data/bike_q1_stops.rds")

# Kopiere Datensatz in Question2 Datensatz und wandel Date/time Spalte in Date spalte um und füge Sie hinzu
bike_q2 <- bike
bike_q2 <- bike_q2 %>%
  mutate(date_start=date(bike_q2$starttime),
         date_stop=date(bike_q2$stoptime))


# Extrahiere das Gender aus dem Datensatz für die Starts
bike_q2_starts_gender <- bike_q2 %>%
  group_by(date_start, start_station_name, start_station_latitude, start_station_longitude, gender) %>%
  summarise( anzahl = n() )

# Extrahiere das Gender aus dem Datensatz für die Stops
bike_q2_stops_gender <- bike_q2 %>%
  group_by(date_stop, end_station_name, end_station_latitude, end_station_longitude, gender) %>%
  summarise( anzahl = n() )

# Tidy Gender Tabelle für starts und stops
gender_q2_starts <- pivot_wider(bike_q2_starts_gender, names_from = gender, values_from = anzahl)

gender_q2_stops <- pivot_wider(bike_q2_stops_gender, names_from = gender, values_from = anzahl)

# Aggregiere Starts und Stops nach Datum und Station

bike_q2_starts <- bike_q2 %>%
  group_by(date_start, start_station_name, start_station_latitude, start_station_longitude) %>%
  summarise(avr_age = round(2016-mean(birth_year)), avg_tripduration = mean(tripduration), anzahl = n() )
bike_q2_stops <- bike_q2 %>%
  group_by(date_stop, end_station_name, end_station_latitude, end_station_longitude) %>%
  summarise(avr_age = round(2016-mean(birth_year)), avg_tripduration = mean(tripduration), anzahl = n() )

# Füge den agregierten Tabellen das Gender hinzu

bike_q2_starts_rdy <- left_join(bike_q2_starts, gender_q2_starts, by = c("date_start", "start_station_name" ))
bike_q2_stops_rdy <- left_join(bike_q2_stops, gender_q2_stops, by = c("date_stop", "end_station_name" ))

# Nicht benötigte Spalten gelöscht und NAs in 0 umgewandelt weil wir wissen das diese 0 sind

bike_q2_starts_rdy$start_station_latitude.y <- NULL
bike_q2_starts_rdy$start_station_longitude.y <- NULL

bike_q2_starts_rdy[is.na(bike_q2_starts_rdy$`1`),"1"] <- 0
bike_q2_starts_rdy[is.na(bike_q2_starts_rdy$`2`),"2"] <- 0
bike_q2_starts_rdy[is.na(bike_q2_starts_rdy$`0`),"0"] <- 0

bike_q2_starts_rdy <- rename(bike_q2_starts_rdy, male = "1")
bike_q2_starts_rdy <- rename(bike_q2_starts_rdy, woman = "2")
bike_q2_starts_rdy <- rename(bike_q2_starts_rdy, undefined = "0")


bike_q2_stops_rdy$end_station_latitude.y <- NULL
bike_q2_stops_rdy$end_station_longitude.y <- NULL

bike_q2_stops_rdy[is.na(bike_q2_stops_rdy$`1`),"1"] <- 0
bike_q2_stops_rdy[is.na(bike_q2_stops_rdy$`2`),"2"] <- 0
bike_q2_stops_rdy[is.na(bike_q2_stops_rdy$`0`),"0"] <- 0

bike_q2_stops_rdy <- rename(bike_q2_stops_rdy, male = "1")
bike_q2_stops_rdy <- rename(bike_q2_stops_rdy, woman = "2")
bike_q2_stops_rdy <- rename(bike_q2_stops_rdy, undefined = "0")

rm(bike_q2, bike_q2_starts, bike_q2_starts_gender, bike_q2_stops, bike_q2_stops_gender, gender_q2_starts, gender_q2_stops)

saveRDS(bike_q2_starts_rdy, "Data/bike_q2_starts.rds")
saveRDS(bike_q2_stops_rdy, "Data/bike_q2_stops.rds")

q2_starts <- read_rds("Data/bike_q2_starts.rds")
q2_stops <- read_rds("Data/bike_q2_stops.rds")

feb2017 <- read_csv("Data/201702-citibike-tripdata.csv")
may2017 <- read_csv("Data/201705-citibike-tripdata.csv")
aug2017 <- read_csv("Data/201708-citibike-tripdata.csv")
nov2017 <- read_csv("Data/201711-citibike-tripdata.csv")


saveRDS(feb2017, "Data/feb2017.rds")
saveRDS(may2017, "Data/may2017.rds")
saveRDS(aug2017, "Data/aug2017.rds")
saveRDS(nov2017, "Data/nov2017.rds")

# Datensätze laden
test_feb2017 <- read_rds("Data/feb2017.rds")
test_may2017 <- read_rds("Data/may2017.rds")
test_aug2017 <- read_rds("Data/aug2017.rds")
test_nov2017 <- read_rds("Data/nov2017.rds")

# Leerzeichen in Spaltennamen ersetzten
names(test_feb2017) <- str_replace_all(names(test_feb2017), c(" " = "_"))
names(test_may2017) <- str_replace_all(names(test_may2017), c(" " = "_"))
names(test_aug2017) <- str_replace_all(names(test_aug2017), c(" " = "_"))
names(test_nov2017) <- str_replace_all(names(test_nov2017), c(" " = "_"))

# Spaltennamen an Rest des Datensatzes anpassen
test_feb2017 <- test_feb2017 %>%
  rename(tripduration = Trip_Duration,
         starttime = Start_Time,
         stoptime = Stop_Time,
         start_station_id = Start_Station_ID,
         start_station_name = Start_Station_Name,
         start_station_latitude = Start_Station_Latitude,
         start_station_longitude = Start_Station_Longitude,
         end_station_id = End_Station_ID,
         end_station_name = End_Station_Name,
         end_station_latitude = End_Station_Latitude,
         end_station_longitude = End_Station_Longitude,
         bikeid = Bike_ID,
         usertype = User_Type,
         birth_year = Birth_Year,
         gender = Gender)

# Einen Testdatensatz erstellen
test_data <- rbind(test_feb2017, test_may2017, test_aug2017, test_nov2017)

# Nicht mehr benötigte Objekte entfernen
rm(test_feb2017, test_aug2017, test_may2017, test_nov2017)

# Stationen für 2017 in einem eigenen Datensatz speichern
stations_2017 <- unique(c(test_data[,5], test_data[,6], test_data[,7]))
stations_2017 <- as.data.frame(stations_2017)
stations_2017 <- stations_2017 %>% distinct()
names(stations_2017) <- c("start_station_name", "start_station_latitude", "start_station_longitude")

# Bezirke laden
nyc_neighborhoods <- readRDS("Data/GEOJSON.rds")

# Stationen für 2017 bearbeiten
stations_2017$start_station_name <- as.factor(stations_2017$start_station_name)

# stations_2017 mit neighborhoods verbinden
points_spdf <- stations_2017
coordinates(points_spdf) <- ~start_station_longitude + start_station_latitude
proj4string(points_spdf) <- proj4string(nyc_neighborhoods)
matches <- over(points_spdf, nyc_neighborhoods)
stations_2017 <- cbind(stations_2017, matches)

# Nur die Spalten behalten, die weiterhin wichtig sind
stations_2017 <- stations_2017 %>%
  dplyr::select(start_station_name, start_station_latitude, start_station_longitude, neighborhood)

# Nicht mehr benötige Objekte entfernen
rm(nyc_neighborhoods, points_spdf, matches)

# Einen Testdatensatz erstellen
hourly_test <- test_data

# Datum und Stunde trennen
hourly_test <- hourly_test %>%
  mutate(start_date = as_date(starttime),
         start_hour = hour(starttime))

# Bezirke für Stationen hinzufügen
hourly_test <- hourly_test %>%
  left_join(stations_2017, by = c("start_station_name"="start_station_name",
                                  "start_station_latitude"="start_station_latitude",
                                  "start_station_longitude"="start_station_longitude"))

# Fehlende Werte überprüfen
apply(hourly_test, 2, function(x) sum(is.na(x)))

# Überprüfen wo der Bezirk fehlt
hourly_test[is.na(hourly_test$neighborhood),]

# Der Bezirk von zwei Stationen ist nicht bekannt, fehlende Werte explizit machen
hourly_test$neighborhood <- fct_explicit_na(hourly_test$neighborhood)

# Nach Bezirk gruppieren und Nutzerzahlen aufsummieren
hourly_test <- hourly_test %>%
  group_by(start_date, start_hour, neighborhood) %>%
  summarise(user_count = n()) %>%
  mutate(weekday = wday(start_date, abbr = F, label = T, week_start = 1),
         holiday = ifelse(isBizday(as.timeDate(start_date), holidayNYSE(2017))==FALSE,1,0),
         month = month(start_date, abbr = F, label = T),
         week = week(start_date))

# Variablen für Wochentag und Monat als ungeordneten Faktor speichern
hourly_test$weekday <- factor(hourly_test$weekday, ordered = FALSE)
hourly_test$month <- factor(hourly_test$month, ordered = FALSE)

# Wetterdaten hinzufügen
hourly_test <- hourly_test %>%
  left_join(hourly_weather_17, by = c("start_date" = "date", "start_hour" = "hour"))

# Fehlende Werte überprüfen
apply(hourly_starts_nh, 2, function(x) sum(is.na(x)))

# Spalte Neighborhood in character umwandeln, um nicht genutzte level zu eliminieren
hourly_test$neighborhood <- as.character(hourly_test$neighborhood)

# Bezirke ausfindig machen, die in 2017 vorkommen, aber nicht in 2016 und diese eliminieren
setdiff(hourly_test$neighborhood, hourly_starts_nh$neighborhood)
hourly_test <- hourly_test %>%
  filter(neighborhood != "Astoria" & neighborhood != "Crown Heights" & neighborhood != "Ditmars Steinway" &
           neighborhood != "Harlem" & neighborhood != "Morningside Heights" & 
           neighborhood != "Prospect Heights" & neighborhood != "Prospect-Lefferts Gardens")

# Daten speichern
saveRDS(hourly_test, "Data/q1_starts_test.RDS")

# Daten laden
hourly_test <- read_rds("Data/q1_starts_test.RDS")

# Testdaten nur für Bezirk Chelsea
hourly_chelsea_test <- hourly_test %>%
  dplyr::select(-month, -week) %>%
  filter(neighborhood == "Chelsea") %>%
  mutate(quarter = quarter(start_date),
         start_datetime = make_datetime(year = year(start_date), month = month(start_date), 
                                        day = day(start_date), hour = start_hour, min = 0, sec = 0))
hourly_chelsea_test <- hourly_chelsea_test[, c(11,1:2,4,3,10,5:9)]

# Dataframe für aggregierte Tesdaten erstellen

test_data_q2 <- test_data

# Nicht benötigte Columns entfernen

test_data_q2$gender <- NULL
test_data_q2$usertype <- NULL

#Datum trennen
test_data_q2 <- test_data_q2 %>%
  mutate(start_date = as_date(starttime))

# stations 2017 umwandeln

stations_2017_test <- stations_2017
stations_2017_test$start_station_name <- as.character(stations_2017_test$start_station_name)
stations_2017_test$neighborhood <- as.character(stations_2017_test$neighborhood)

#Bezirke für Stationen hinzufügen

test_data_q2_test <- test_data_q2 %>%
  left_join(stations_2017, by = c("start_station_name"="start_station_name",
                                  "start_station_latitude"="start_station_latitude",
                                  "start_station_longitude"="start_station_longitude"))


test_data_q2 <- test_data_q2 %>%
  left_join(stations_2017_test, by = c("start_station_name"="start_station_name",
                                  "start_station_latitude"="start_station_latitude",
                                  "start_station_longitude"="start_station_longitude"))



# Points1 benutzen damit man nur die Stations kriegt die auch in den Trainingsdaten vorhanden sind

'points1_test <- points1
points1_test$start_station_name <- as.character(points1_test$start_station_name)
points1_test$neighborhood <- as.character(points1_test$neighborhood)


points1_test <- points1_test %>%
  rename(start_station_latitude = start_station_latitude.x,
         start_station_longitude = start_station_longitude.x)


#Bezirke für Stationen hinzufügen
test_data_q2 <- test_data_q2 %>%
  left_join(points1_test, by = c("start_station_name"="start_station_name",
                                  "start_station_latitude"="start_station_latitude",
                                  "start_station_longitude"="start_station_longitude"))'

#Fehlende Werte überprüfen
#apply(test_data_q2, 2, function(x) sum(is.na(x)))

#Überprüfen wo der Bezirk fehlt
test_data_q2[is.na(test_data_q2$neighborhood),]

#Der Bezirk von zwei Stationen ist nicht bekannt, fehlende Werte explizit machen
test_data_q2$neighborhood <- fct_explicit_na(test_data_q2$neighborhood)

#Spalte Neighborhood in character umwandeln, um nicht genutzte level zu eliminieren
test_data_q2$neighborhood <- as.character(test_data_q2$neighborhood)

test_data_q2$birth_year <- as.numeric(test_data_q2$birth_year)

# Löschen der fehlenden Werte mit Birthyear

test_data_q2 <- na.omit(test_data_q2)

#Nach Bezirk gruppieren und Nutzerzahlen aufsummieren
test_data_q2 <- test_data_q2 %>%
  group_by(start_date, neighborhood) %>%
  summarise(anzahl = n(), avg_age = round(2016-mean(birth_year)), avg_tripduration = mean(tripduration)) %>%
  mutate(weekday = wday(start_date, abbr = F, label = T, week_start = 1),
         holiday = ifelse(isBizday(as.timeDate(start_date), holidayNYSE(2017))==FALSE,1,0),)

#Fehlende Werte überprüfen
apply(test_data_q2, 2, function(x) sum(is.na(x)))

#Wetter hinzufügen

test_data_q2 <- left_join(test_data_q2, weather_2017, by = c("start_date" = "date"))

# Columns wie bei den Trainingsdaten anordnen

test_data_q2 <- test_data_q2[, c(1,2,3,5,4,8,9,10,11,12,13,6,7)]

rm(stations_2017_test, test_data_q2_test, points, points1_test)


#Bezirke ausfindig machen, die in 2017 vorkommen, aber nicht in 2016 und diese eliminieren
setdiff(test_data_q2$neighborhood, q2_starts_w2_agg$neighborhood)
test_data_q2 <- test_data_q2 %>%
  filter(neighborhood != "Astoria" & neighborhood != "Crown Heights" & neighborhood != "Ditmars Steinway" &
           neighborhood != "Harlem" & neighborhood != "Morningside Heights" & 
           neighborhood != "Prospect Heights" & neighborhood != "Prospect-Lefferts Gardens")


#Monat und Woche als Variablen eingefügt

#test_data_q2 <- test_data_q2 %>%
  #mutate(week = week(start_date))

test_data_q2 <- test_data_q2 %>%
  mutate(month = month(start_date, abbr = F, label = T))


#Variable für Wochentag als ungeordneten Faktor speichern
test_data_q2$weekday <- factor(test_data_q2$weekday, ordered = FALSE)
test_data_q2$month <- factor(test_data_q2$month, ordered = FALSE)

#Als .RDS speichern
saveRDS(test_data_q2, "Data/test_data_q2.RDS")

test_data_q2 <- read_rds("Data/test_data_q2.RDS")

#Regression mit daily_neighborhood_train
options(max.print=150)
daily_neighborhood_model <- as.formula(anzahl ~ .)
lin_reg_daily_neighborhood <- lm(daily_neighborhood_model, daily_neighborhood_train)
summary(lin_reg_daily_neighborhood)

#Vorhersage mit Testdaten

lin_reg_q2n_predictions <- predict(lin_reg_daily_neighborhood, daily_neighborhood_test)

test_q2_mse <- mean((daily_neighborhood_test$anzahl - lin_reg_q2n_predictions) ^ 2)
test_q2_rmse <- sqrt(mean((daily_neighborhood_test$anzahl - lin_reg_q2n_predictions) ^ 2))
test_q2_mse

test_q2_rmse

daily_vizdata <- data.frame( Date = daily_neighborhood_test$start_date, Values = daily_neighborhood_test$anzahl, LinReg = lin_reg_q2n_predictions)
ggplot() +
  labs(title = "Lineare Regression Vorhersage Täglicher Nutzerzahlen") +
  xlab("Date") + ylab("Anzahl") +
  geom_line(data = daily_vizdata, aes(y = Values, x = Date, color = "black")) +
  geom_line(data = daily_vizdata, aes(y = LinReg, x = Date, color = "orange")) +
  theme_minimal() +
  theme(plot.title = element_text(hjust = 0.5),
        panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
        panel.border = element_blank(),
        axis.line = element_line(colour = "black"))+
  facet_wrap(month(daily_neighborhood_test$start_date, label = T, abbr = F), scales = "free_x") +
  scale_color_manual(name = "Legende", values = c("black" = "black", "orange" = "orange"), labels = c("Tatsächliche Nutzer", "Lineare Regression Vorhersage"))

# ARIMAX für Bestimmung der lags mit Trainingsdaten
hourly.arimax.Y <- as.numeric(hourly_chelsea_train$user_count)
hourly.arimax.X <- as.matrix(hourly_chelsea_train[,c(5:40)])
hourly_arimax <- auto.arima(hourly.arimax.Y, xreg = hourly.arimax.X)

hourly_lags <- hourly_arimax$arma[1]

hourly.ann.lags <- as.lags(hourly_lags, type = "multivariate")
hourly.ann.timesteps <- as.timesteps(hourly.ann.lags, type = "multivariate")

# Trainingsdaten mit gelaggtem y als x vorbereiten
hourly_tsdiff <- as.data.frame(build_stationary(hourly_chelsea_train$user_count, k = hourly.ann.lags))
if (hourly.ann.lags > 0) {
  hourly_tsdiff <- hourly_tsdiff[c(-1:-hourly.ann.lags),]
  colnames(hourly_tsdiff) <- c("user_count_diff", "user_count_diff_lag1","user_count_diff_lag2",
                        "user_count_diff_lag3", "user_count_diff_lag4","user_count_diff_lag5")
}

hourly.df.ann <- cbind.data.frame(hourly_chelsea_train[c((hourly.ann.lags+2):nrow(hourly_chelsea_train)),], hourly_tsdiff)
hourly.df.ann <- hourly.df.ann[,c(1,3:4,41:46,5:40)]

hourly.Y.train.resample <- resample.y(hourly.df.ann$user_count_diff, hourly.ann.timesteps)
hourly.X.train.resample <- resample.X(hourly.df.ann[,5:45], hourly.ann.timesteps)
hourly.df.resample <- cbind.data.frame(hourly.Y.train.resample, hourly.X.train.resample)
colnames(hourly.df.resample)[1] <- c("user_count_diff")

hourly.df.resample$start_datetime <- hourly.df.ann$start_datetime[(hourly.ann.timesteps):nrow(hourly.df.ann)]
hourly.df.resample <- hourly.df.resample[,c(248, 1:247)]

# Testdaten mit gelaggtem y als x vorbereiten
hourly_tsdiff_test <- as.data.frame(build_stationary(hourly_chelsea_test$user_count, k = hourly.ann.lags))
if (hourly.ann.lags > 0) {
  hourly_tsdiff_test <- hourly_tsdiff_test[c(-1:-hourly.ann.lags),]
  colnames(hourly_tsdiff_test) <- c("user_count_diff", "user_count_diff_lag1","user_count_diff_lag2",
                        "user_count_diff_lag3", "user_count_diff_lag4","user_count_diff_lag5")
}

hourly.df.ann.test <- cbind.data.frame(hourly_chelsea_test[c((hourly.ann.lags+2):nrow(hourly_chelsea_test)),], hourly_tsdiff_test)
hourly.df.ann.test <- hourly.df.ann.test[,c(1,3:4,41:46,5:40)]

hourly.Y.test.resample <- resample.y(hourly.df.ann.test$user_count_diff, hourly.ann.timesteps)
hourly.X.test.resample <- resample.X(hourly.df.ann.test[,5:45], hourly.ann.timesteps)
hourly.df.resample.test <- cbind.data.frame(hourly.Y.test.resample, hourly.X.test.resample)
colnames(hourly.df.resample.test)[1] <- c("user_count_diff")

hourly.df.resample.test$start_datetime <- hourly.df.ann.test$start_datetime[(hourly.ann.timesteps):nrow(hourly.df.ann.test)]
hourly.df.resample.test <- hourly.df.resample.test[,c(248, 1:247)]


hourly.df.train <- hourly.df.resample
hourly.df.test <- hourly.df.resample.test